package com.corejsf;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Collections;
import java.util.GregorianCalendar;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;
import java.util.Set;

import javax.inject.Inject;
import javax.inject.Named;
import javax.sql.DataSource;
import javax.annotation.PostConstruct;
import javax.annotation.Resource;
import javax.enterprise.context.ConversationScoped;

@SuppressWarnings("serial")
@Named
@ConversationScoped
/**
 * The data sheet tables will draw all data from this class.
 * This class access' the activeUser and the list of worksheets from
 * the user.
 * @author Jitin Dhillon, Kelton Kowalchuk
 *
 */
public class TableData implements Serializable {
	/**
	 * Database resource.
	 */
	@Resource(mappedName = "java:jboss/datasources/worksheets")
	private static DataSource dataSource;
	@Inject
	Login login;
	/**
	 * The employee number of the activeUser.
	 */
	int eNum = Login.getActiveUser().geteNumber();
	/**
	 * The name of the activeUser.
	 */
	String name = Login.getActiveUser().getFullname();
	/**
	 * Map containing all the worksheets of the activeUser;
	 */
	Map<Integer, ArrayList<WorkSheet>> sheetMap;
	/**
	 * List of all the weeks that have a sheet.
	 */
	private ArrayList<Integer> weeks;
	
	/**
	 * Select value for the drop down week list.
	 */
	private int weekValue;
	
	/**
	 * Maximum row ID which is used to insert unique row numbers into the database rows.
	 */
	private int maxRowID = 0;
	/**
	 * Contains the selected week for the week drop down menu.
	 */
	private int selectedWeek;
	
	/**
	 * Contains the rows of data for the Worksheet.
	 */
	private static ArrayList<WorkSheet> names;
	
	/**
	 * True if the selected worksheet week matched the current week of the year.
	 */
	private boolean isCurrentWeek;
	
	/**
	 * Get the current calendar date.
	 */
	Calendar date = Calendar.getInstance();
	
	/**
	 * Current week of the month.
	 */
	final int week;

	/**
	 * Current month of the year.
	 */
	
	int month = date.get(Calendar.MONTH);
	/**
	 * The week end day for the current week.
	 */
	String weekEnd;
	
	/* Total Values for table footers */
	private double tsun;
	private double tmon;
	private double ttue;
	private double twed;
	private double tthu;
	private double tfri;
	private double tsat;
	private double ttotal;
	/* ^Total Values for table footers^ */
	
	@PostConstruct
	/**
	 * Function that will automatically run and figure out the end of the week. Will
	 * also check if the current week currently does not exist in the database and add
	 * temporary rows.
	 */
	public void createCalendar() {
		SimpleDateFormat bartDateFormat = new SimpleDateFormat("MMMM d, yyyy");
		GregorianCalendar cal = new GregorianCalendar();
		cal.setTime(new Date());
		cal.set(GregorianCalendar.WEEK_OF_YEAR, week);
		cal.set(GregorianCalendar.DAY_OF_WEEK, GregorianCalendar.FRIDAY);
		weekEnd = bartDateFormat.format(cal.getTime());
		loadData();
		
		//Add 5 rows into the database for a new week.
		if (!sheetMap.containsKey(week)) {
			add();
			add();
			add();
			add();
			add();
			loadData();
		}
		
		//Exract the weeks from the map to display in the drop down menu.
		Set<Integer> keys = sheetMap.keySet();
		weeks.clear();
		for (int i : keys) {
			weeks.add(i);
		}

		// copy the worksheet into bean variable.
		names = sheetMap.get(week);
		isCurrentWeek = true;

		calcColumns(names);
	}
	
	/**
	 * Will check if the projectID WP pair is a unique combo.
	 * @param project Project ID
	 * @param wp WP
	 * @return true if unique, false otherwise.
	 */
	public boolean checkValid(int project, String wp) {
		int count = 0;
		for (WorkSheet s : names) {
				if (s.getProject() == project) {
					if (s.getWp().equals(wp))
						return false;
				}
		}
		return true;
	}

	/**
	 * Constructor.
	 */
	TableData() {
		// Set the current week.
		week = date.get(Calendar.WEEK_OF_YEAR);

		selectedWeek = week;

		weeks = new ArrayList<Integer>();
		sheetMap = new HashMap<Integer, ArrayList<WorkSheet>>();
	}

	/**
	 * Generates an available project id.
	 * @return project id.
	 */
	public int newprojectid() {
		if (!sheetMap.containsKey(week)) {
			return 0;
		}
		int i;
		for (i = 0;; i++) {
			int count = 0;
			for (WorkSheet s : names) {
				// System.out.println("i value: " + i + "Sheet: " +
				// s.getProject());
				if (i == s.getProject()) {
					count++;
				}
			}
			if (count == 0)
				break;
		}
		return i;
	}
	
	/**
	 * Loads data from the database and inserts into local map.
	 */
	public void loadData() {
		sheetMap.clear();
		PreparedStatement stmt = null;
		Connection connection = null;
		try {
			try {
				connection = dataSource.getConnection();
				try {
					stmt = connection
							.prepareStatement("SELECT * FROM sheets WHERE eNumber = ?");
					stmt.setInt(1, Login.getActiveUser().geteNumber());
					ResultSet rs = stmt.executeQuery();
					while (rs.next()) {
						int week = rs.getInt("Week");
						if (!sheetMap.containsKey(week)) {
							sheetMap.put(week, new ArrayList<WorkSheet>());
						}
						int projectid = rs.getInt("ProjectID");
						String wp = rs.getString("WP");
						double sat = rs.getDouble("Saturday");
						double sun = rs.getDouble("Sunday");
						double mon = rs.getDouble("Monday");
						double tue = rs.getDouble("Tuesday");
						double wed = rs.getDouble("Wednesday");
						double thu = rs.getDouble("Thursday");
						double fri = rs.getDouble("Friday");
						String notes = rs.getString("Notes");
						int rowID = rs.getInt("RowID");
						if (rowID >= maxRowID) {
							maxRowID = rowID + 1;
						}
						WorkSheet sheet = new WorkSheet(projectid, wp, sat,
								sun, mon, tue, wed, thu, fri, notes, false,
								rowID);
						sheetMap.get(week).add(sheet);
					}
				} finally {
					if (stmt != null) {
						stmt.close();
					}
				}
			} finally {
				if (connection != null) {
					connection.close();
				}
			}
		} catch (SQLException ex) {
			System.out.println("Error in persist ");
			ex.printStackTrace();
		}
		names = sheetMap.get(week);
	}

	/**
	 * Calculates column totals for hours.
	 * @param sheet the sheet to calculate totals for.
	 */
	public void calcColumns(ArrayList<WorkSheet> sheet) {
		double tsun1 = 0;
		double tmon1 = 0;
		double ttue1 = 0;
		double twed1 = 0;
		double tthu1 = 0;
		double tfri1 = 0;
		double tsat1 = 0;
		for (WorkSheet s : sheet) {
			tsun1 += s.getSun();
			tmon1 += s.getMon();
			ttue1 += s.getTue();
			twed1 += s.getWed();
			tthu1 += s.getThu();
			tfri1 += s.getFri();
			tsat1 += s.getSat();
		}

		tsun = tsun1;
		tmon = tmon1;
		ttue = ttue1;
		twed = twed1;
		tthu = tthu1;
		tfri = tfri1;
		tsat = tsat1;
		ttotal = tsun + tmon + ttue + twed + tthu + tfri + tsat;
	}

	/**
	 * returns the array of worksheets.
	 * @return the arrayList of worksheet rows.
	 */
	public ArrayList<WorkSheet> getNames() {
		return names;
	}

	/**
	 * Saves all information in the worksheet. 
	 * @return
	 */
	public String save() {
		for (WorkSheet name : names) {
			if (checkValid(name.getProject(), name.getWp())) {
				name.setEditable(false);
				name.calcTotal();
				PreparedStatement stmt = null;
				Connection connection = null;
				try {
					try {
						connection = dataSource.getConnection();
						try {
							System.out.println("Going to update.");
							stmt = connection
									.prepareStatement("UPDATE sheets SET Saturday = ?, Sunday = ?, Monday = ?,"
											+ "Tuesday = ?, Wednesday = ?, Thursday = ?, Friday = ?, Notes = ?, "
											+ "ProjectID = ?, WP = ? WHERE eNumber = ? AND RowID = ?");
							stmt.setDouble(1, name.getSat());
							stmt.setDouble(2, name.getSun());
							stmt.setDouble(3, name.getMon());
							stmt.setDouble(4, name.getTue());
							stmt.setDouble(5, name.getWed());
							stmt.setDouble(6, name.getThu());
							stmt.setDouble(7, name.getFri());
							stmt.setString(8, name.getNotes());
							stmt.setInt(9, name.getProject());
							stmt.setString(10, name.getWp());
							stmt.setInt(11, Login.getActiveUser().geteNumber());
							stmt.setInt(12, name.getRowID());
							stmt.executeUpdate();
							System.out.println("Project: " + name.getProject()
									+ " WP: " + name.getWp());

						} finally {
							if (stmt != null) {
								stmt.close();
							}
						}
					} finally {
						if (connection != null) {
							connection.close();
						}
					}
				} catch (SQLException ex) {
					System.out.println("Error in persist ");
					ex.printStackTrace();
				}

			}
			loadData();
			calcColumns(names);
		}
		return null;
	}

	/**
	 * Log outs the current user. And sets the activeUser to null.
	 * @return navigation to the
	 */
	public String logout() {
		login.setActiveUser(null);
		login.setPassword("");
		login.setPassword("");
		login.getConversation().end();
		return "login?faces-redirect=true";
	}

	/**
	 * Delete the row from the worksheet.
	 * @param sheet
	 */
	public void delete(WorkSheet sheet) {
		names.remove(sheet);
		PreparedStatement stmt = null;
		Connection connection = null;
		try {
			try {
				connection = dataSource.getConnection();
				try {
					stmt = connection
							.prepareStatement("DELETE FROM sheets WHERE ProjectID = ? AND WP = ?");
					stmt.setInt(1, sheet.getProject());
					stmt.setString(2, sheet.getWp());
					stmt.executeUpdate();
				} finally {
					if (stmt != null) {
						stmt.close();
					}
				}
			} finally {
				if (connection != null) {
					connection.close();
				}
			}
		} catch (SQLException ex) {
			System.out.println("Error in persist ");
			ex.printStackTrace();
		}
	}

	/**
	 * Add a empty row to the worksheet.
	 */
	public void add() {
		PreparedStatement stmt = null;
		Connection connection = null;
		try {
			try {
				connection = dataSource.getConnection();
				try {
					stmt = connection
							.prepareStatement("INSERT INTO sheets VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
					/* STILL TO DO */
					stmt.setInt(1, Login.getActiveUser().geteNumber());
					stmt.setInt(2, selectedWeek);
					stmt.setInt(3, newprojectid());
					stmt.setString(4, "TmpID");
					stmt.setDouble(5, 0.0);
					stmt.setDouble(6, 0.0);
					stmt.setDouble(7, 0.0);
					stmt.setDouble(8, 0.0);
					stmt.setDouble(9, 0.0);
					stmt.setDouble(10, 0.0);
					stmt.setDouble(11, 0.0);
					stmt.setString(12, "N/C");
					stmt.setInt(13, maxRowID);
					stmt.executeUpdate();

				} finally {
					if (stmt != null) {
						stmt.close();
					}
				}
			} finally {
				if (connection != null) {
					connection.close();
				}
			}
		} catch (SQLException ex) {
			System.out.println("Error in persist ");
			ex.printStackTrace();
		}
		loadData();
	}

	/**
	 * Changes to a previous week worksheet and checks if it is the current week
	 * and wether or not to not display the edit or delete columns. If changing
	 * back to current week it will display the editable fields. .
	 */
	public void changeWeek() {
		names = sheetMap.get(weekValue);
		if (weekValue == week) {
			isCurrentWeek = true;
		} else {
			isCurrentWeek = false;
			for (WorkSheet name : names) {
				name.setEditable(false);
				name.calcTotal();
			}
		}
		selectedWeek = weekValue;
		SimpleDateFormat bartDateFormat = new SimpleDateFormat("MMMM d, yyyy");
		GregorianCalendar cal = new GregorianCalendar();
		cal.setTime(new Date());
		cal.set(GregorianCalendar.WEEK_OF_YEAR, selectedWeek);
		cal.set(GregorianCalendar.DAY_OF_WEEK, GregorianCalendar.FRIDAY);
		weekEnd = bartDateFormat.format(cal.getTime());
		Collections.sort(weeks);
		calcColumns(names);

	}
	//Getters setters.
	public String getWeekEnd() {
		return weekEnd;
	}

	public void setWeekEnd(String weekEnd) {
		this.weekEnd = weekEnd;
	}

	public ArrayList<Integer> getWeeks() {
		return weeks;
	}

	public void setWeeks(ArrayList<Integer> weeks) {
		this.weeks = weeks;
	}

	public int getSelectedWeek() {
		return selectedWeek;
	}

	public void setSelectedWeek(int selectedWeek) {
		this.selectedWeek = selectedWeek;
	}

	public int getWeekValue() {
		return weekValue;
	}

	public void setWeekValue(int weekValue) {
		this.weekValue = weekValue;
	}

	public boolean getIsCurrentWeek() {
		return isCurrentWeek;
	}

	public void setIsCurrentWeek(boolean isCurrentWeek) {
		this.isCurrentWeek = isCurrentWeek;
	}

	public double getTsun() {
		return tsun;
	}

	public void setTsun(int tsun) {
		this.tsun = tsun;
	}

	public double getTmon() {
		return tmon;
	}

	public void setTmon(int tmon) {
		this.tmon = tmon;
	}

	public double getTtue() {
		return ttue;
	}

	public void setTtue(int ttue) {
		this.ttue = ttue;
	}

	public double getTwed() {
		return twed;
	}

	public void setTwed(int twed) {
		this.twed = twed;
	}

	public double getTthu() {
		return tthu;
	}

	public void setTthu(int tthu) {
		this.tthu = tthu;
	}

	public double getTfri() {
		return tfri;
	}

	public void setTfri(int tfri) {
		this.tfri = tfri;
	}

	public double getTsat() {
		return tsat;
	}

	public void setTsat(int tsat) {
		this.tsat = tsat;
	}

	public double getTtotal() {
		return ttotal;
	}

	public void setTtotal(double ttotal) {
		this.ttotal = ttotal;
	}
	
	public int geteNum() {
		return eNum;
	}

	public void seteNum(int eNum) {
		this.eNum = eNum;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public int getWeek() {
		return week;
	}
}